package com.guli.common.utils;

import com.alibaba.fastjson.JSON;
import org.springframework.util.CollectionUtils;

import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DatabaseScanner {
    private static final String URL_REGEX = "(https?://[\\w.-]+\\.[a-zA-Z]{2,6})";
    private static final Pattern URL_PATTERN = Pattern.compile(URL_REGEX,Pattern.CASE_INSENSITIVE);


    private static boolean isUrl(String value) {
        return URL_PATTERN.matcher(value).find();
    }

    private static Set<String> getUrSet(String value) {
        // 存储匹配结果的集合
        Set<String> matches = new HashSet<>();
        Matcher matcher = URL_PATTERN.matcher(value);
        while (matcher.find()) {
            matches.add(matcher.group());
        }
        return matches;
    }

    static Map<String, Set<String>> dataMap = new HashMap<>();

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://test.database3600.scsite.net:3600/finance_order_center?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String user = "souche_rw";
        String password = "1Qye6Iyj6AD9idZR";

        scanDatabase(url, user, password, dataMap);
        // 这里可以对results进行进一步处理或输出
        System.out.println(JSON.toJSONString(dataMap));
    }

    public static void scanDatabase(String url, String user, String password, Map<String, Set<String>> dataMap) throws SQLException {
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            // 1. 获取所有表名
            DatabaseMetaData metaData = conn.getMetaData();
            try (ResultSet tables = metaData.getTables("finance_order_center", null, "%", new String[]{"TABLE"})) {
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    System.out.println("扫描表;" + tableName);
                    scanTable(conn, tableName, dataMap);
                }
            }
        }
    }

    private static void scanTable(Connection conn, String tableName, Map<String, Set<String>> dataMap) throws SQLException {
        // 2. 获取表的所有文本字段
        DatabaseMetaData metaData = conn.getMetaData();
        try (ResultSet columns = metaData.getColumns("finance_order_center", null, tableName, null)) {
            List<String> textColumns = new ArrayList<>();
            while (columns.next()) {
                if (isTextType(columns.getInt("DATA_TYPE"))) {
                    textColumns.add(columns.getString("COLUMN_NAME"));
                }
            }

            // 3. 按行扫描并检查字段值
            scanRows(conn, tableName, textColumns, dataMap);
        }
    }

    private static void scanRows(Connection conn, String tableName, List<String> textColumns, Map<String, Set<String>> dataMap) throws SQLException {
        StringBuilder query = new StringBuilder("SELECT ");
        query.append(String.join(", ", textColumns)).append(" FROM ").append(tableName);

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query.toString())) {

            while (rs.next()) {
                for (int i = 0; i < textColumns.size(); i++) {
                    String value = rs.getString(i + 1);
                    if (value != null && isUrl(value)) {
                        Set<String> tempSet = getUrSet(value);
                        Set<String> domainSet = dataMap.get(tableName + "," + textColumns.get(i));
                        if (CollectionUtils.isEmpty(domainSet)) {
                            domainSet = new HashSet<>();
                            domainSet.addAll(tempSet);
                            dataMap.put(tableName + "," + textColumns.get(i), domainSet);
                        } else {
                            domainSet.addAll(tempSet);
                        }
                    }
                }
            }
        }
    }

    private static String getHost(String value) {
        try {
            return new URL(value).getHost();
        } catch (Exception e) {
            return null;
        }
    }

    private static boolean isTextType(int sqlType) {
        return sqlType == Types.VARCHAR || sqlType == Types.CHAR
                || sqlType == Types.LONGVARCHAR || sqlType == Types.CLOB
                || sqlType == Types.NVARCHAR || sqlType == Types.NCHAR
                || sqlType == Types.LONGNVARCHAR || sqlType == Types.NCLOB;
    }


}
